<? 
	session_start();
						
	include_once ('core/conn.php');
							
	$db = get_conn();
							
	check_session();
	$sql = "delete from payments ";
	$result = mysql_query($sql);
 	if(!$result){
 		error_handling('invoices.insert  - '.$sql,mysql_error());
 	}


	$sql = "delete from invoices ";
	$result = mysql_query($sql);
 	if(!$result){
 		error_handling('invoices.insert  - '.$sql,mysql_error());
 	}


	$sql = "delete from residents ";
	$result = mysql_query($sql);
 	if(!$result){
 		error_handling('locations.insert  - '.$sql,mysql_error());
 	}

	$sql = "delete from locations ";
	$result = mysql_query($sql);
 	if(!$result){
 		error_handling('locations.insert  - '.$sql,mysql_error());
 	}				  
	
	$sql = "delete from users where id_user > 1;";
	$result = mysql_query($sql);
 	if(!$result){
 		error_handling('locations.insert  - '.$sql,mysql_error());
 	}		
	
	
	
	$sql = "delete from user_rol where id_user > 1;";
	$result = mysql_query($sql);
 	if(!$result){
 		error_handling('user_rol.insert  - '.$sql,mysql_error());
 	}		
				


	$import = "http://localhost/db.csv";
	$lines = file ($import);
	foreach ($lines as $line_num => $line){
		$info = explode(",",$line);
		
		$number = clean_me($info[0]);
		$filial = strtoupper(clean_me($info[1]));
		$nombre = clean_me($info[3]);
		$apellido1 = clean_me($info[4]);
		$apellido2  = clean_me($info[5]);
		$ID = clean_me($info[6]);
		$comments = clean_me($info[7]);
		$email  = clean_me($info[8] );
		$email2 = clean_me($info[9]); 
		$tel1 = clean_me($info[10]);
		$tel2 = clean_me($info[11]);
		$tel3 = clean_me($info[12]); 
		$workplace = clean_me($info[13]);
		$ocupation = clean_me($info[14]);
		$movin_date = clean_me($info[15]);
		$contact = clean_me($info[16]);
		$prop_type = clean_me($info[17]);
		$vefied = clean_me($info[18]);
		$cars  = clean_me($info[19]);
		$car_desc = clean_me($info[20]); 
		
		$id_facility = 1;
		$passwd  = $number;
		
		if($prop_type ==''){
			$prop_type ="casa";	
		}
		
		$id_location = get_location_by_name($id_facility, $number, $filial, $tel1, $prop_type);
		
		if(get_user_count($id_location)==0){
			create_user($number, $number, $id_location);					
		}
		
			
		
		$sql = "INSERT INTO residents (name,lastname,lastname2,id_location,id_number,email,email2,comments,
					phone1,phone2,phone3,workplace,ocupation,moving_date) VALUES (
		\"".htmlspecialchars($nombre)."\",
		\"".htmlspecialchars($apellido1)."\",
		\"".htmlspecialchars($apellido2)."\",
		".$id_location.",
		\"".$ID."\",
		\"".htmlspecialchars($email)."\",
		\"".htmlspecialchars($email2)."\",
		\"".htmlspecialchars($comments)."\",
		\"".htmlspecialchars($tel1)."\",
		\"".htmlspecialchars($tel2)."\",
		\"".htmlspecialchars($tel3)."\",
		\"".htmlspecialchars($workplace)."\",
		\"".htmlspecialchars($ocupation)."\",
		\"".htmlspecialchars($movin_date)."\")";
			
			
		$result = mysql_query($sql);
		if(!$result){
			error_handling('users.insert  - '.$sql,mysql_error());
		}
		
		if($cars!=''){
			$sql = "INSERT INTO vehicules (id_location,id) VALUES (".$id_location.",\"".htmlspecialchars($cars)."\")";
			$result = mysql_query($sql);
			if(!$result){
				error_handling('vehicules.insert  - '.$sql,mysql_error());
			}					
		}
		
	}// for

	echo "Done";
						
						
function get_location_by_name($id_facility, $number, $acc_id, $phone, $prop_type){
	
	$sql = "select id_location from locations where number='".$number."'";
	$result = mysql_query($sql);
 	if($result){
		$row = mysql_fetch_array($result);
		if($row['id_location'] >0 ){
			return $row['id_location'];	
		}else{
			$sql = "INSERT INTO locations (id_facility, number, acc_id, phone, ltype) VALUES 
					(".$id_facility.",\"".strtoupper($number)."\",\"".htmlspecialchars($acc_id)."\",\"".$phone."\",\"".$prop_type."\")";
			$result = mysql_query($sql);
			if($result){
				$id_location = mysql_insert_id();
				return $id_location;
			}else{
				error_handling('locations.insert  - '.$sql,mysql_error());
			}				  
		}
 	}else{
 		error_handling('locations.insert  - '.$sql,mysql_error());
 	}
}

function get_user_count($id_location){
	if($id_location !=''){
		$sql = "select count(id_user) as cant from users where id_location=".$id_location;
		$result = mysql_query($sql);
		if($result){
			$row = mysql_fetch_array($result);
			return $row['cant'];
		}else{
			error_handling("get_user_count: ".$sql,mysql_error());
		}		
	}	
}

function clean_me($text){
	$text = trim($text);	
	$text = rtrim($text);		
	$text = ltrim($text);			
	$text = strtolower($text);
	$text = ucwords($text);
	//$text = str_replace('�','',$text);	

	return $text;
}
						
function create_user($name,$passwd,$id_location){
	$sql = "INSERT INTO users (name,passwd,id_location,ustatus) VALUES (
		\"".htmlspecialchars($name)."\",
		\"".htmlspecialchars($passwd)."\",
		".$id_location.",
		'A')";
			
			
		$result = mysql_query($sql);
		if(!$result){
			error_handling('users.insert  - '.$sql,mysql_error());
		}else{
			$current =  mysql_insert_id();
			add_roles(2,$current);						
			//save_options($_POST['id_option'],$current);
				
		}	
}					
?>